package com.aliyun.heiyu.powermaster.server.dao.impl;

import com.aliyun.heiyu.common.util.JdbcUtil;
import com.aliyun.heiyu.powermaster.server.dao.DeviceCommonDao;
import com.aliyun.heiyu.powermaster.server.dao.entity.DeviceCommonEntity;
import com.aliyun.heiyu.powermaster.server.dao.entity.DeviceIPDeviceIdDTO;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;

public class DeviceCommonImpl implements DeviceCommonDao {

    public List<DeviceCommonEntity> findAllEntity() {
        String sql = "SELECT * FROM device_common WHERE status = 1 ";
        List<DeviceCommonEntity> deviceCommonEntities = JdbcUtil.queryForList(sql, null, DeviceCommonEntity.class);
        return deviceCommonEntities;
    }

    @Override
    public DeviceCommonEntity findOneEntity(String deviceId) {
        String sql = "SELECT  `device_id`" +
            "    , `type`" +
            "    , `located_cabinet`" +
            "    , `business_type`" +
            "    , `ip`" +
            "    ,`room`" +
            "    , `create_time`" +
            "    , `device_model`" +
            "    , `administrator`" +
            "    , `data_center`" +
            "    , `purchase_date`" +
            "    ,    `rated_power`" +
            "    , `usage_status`" +
            "    , `location_id`" +
            "    ,`remarks`" +
            "    , `status`" +
            "    , `modified_time`" +
            "    , `save_function`" +
            "    , `incompatible_feature`" +
            "    , `manufacturer`" +
            "    , `addr` FROM device_common WHERE status = ? AND device_id = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = deviceId;
        DeviceCommonEntity entity = JdbcUtil.queryForBean(sql, params, DeviceCommonEntity.class);
        return entity;
    }

    @Override
    public DeviceCommonEntity findByIp(String ip) {
        String sql = "SELECT * FROM device_common WHERE status = ? AND ip = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = ip;
        DeviceCommonEntity entity = JdbcUtil.queryForBean(sql, params, DeviceCommonEntity.class);
        return entity;
    }

    @Override
    public DeviceCommonEntity findByLocatedId(String locatedId) {
        String sql = "SELECT * FROM device_common WHERE status = ? AND location_id = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = locatedId;
        DeviceCommonEntity entity = JdbcUtil.queryForBean(sql, params, DeviceCommonEntity.class);
        return entity;
    }

    @Override
    public int getCountOfCabinet(String id) {
        String sql = "SELECT COUNT(device_id) count FROM device_common WHERE status = 1 AND located_cabinet = ? and type=?";
        Object[] params = new Object[2];
        params[0] = id;
        params[1] = "server";
        Map<String, String> resultMap = JdbcUtil.queryForMap(sql, params);
        if (resultMap == null || resultMap.get("count") == null) {
            return 0;
        }
        return Integer.valueOf(resultMap.get("count"));
    }

    @Override
    public int getTotalCountByCondition(String cabinetSn, String condition, Object[] types, String business, String usageStatus, String locationId) {
        ArrayList<Object> list = new ArrayList<>();
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("SELECT COUNT(*) count FROM device_common WHERE status = 1");
        if (condition != null && !"".equals(condition)) {
            condition = condition + "%";
            sqlBuffer.append(" AND (device_id LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("ip LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("administrator LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("manufacturer LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("room LIKE ?) ");
            list.add(condition);
        }
        if (types != null && types.length != 0) {
            int length = types.length;
            sqlBuffer.append(" AND type IN (");
            for (int i = 0; i < length; i++) {
                if (i != length - 1) {
                    sqlBuffer.append("?,");
                } else {
                    sqlBuffer.append("?)");
                }
                list.add(types[i].toString());
            }
        }
        if (cabinetSn != null && !"".equals(cabinetSn)) {
            sqlBuffer.append(" AND located_cabinet = ?");
            list.add(cabinetSn);
        }
        if (usageStatus != null && !"".equals(usageStatus)) {
            sqlBuffer.append(" AND usage_status = ?");
            list.add(usageStatus);
        }
        if (business != null && !"".equals(business)) {
            sqlBuffer.append(" AND business_type = ?");
            list.add(business);
        }
        if (locationId != null && !"".equals(locationId)) {
            sqlBuffer.append(" AND location_id = ?");
            list.add(locationId);
        }
        String sql = sqlBuffer.toString();
        Object[] params = list.toArray();
        Map<String, String> resultMap = JdbcUtil.queryForMap(sql, params);
        if (resultMap == null || resultMap.get("count") == null) {
            return 0;
        }
        return Integer.valueOf(resultMap.get("count"));
    }

    @Override
    public List<DeviceCommonEntity> findByCondition(String cabinetSn, String condition, Object[] types, String business, String status, int start, int size, String locationId) {
        ArrayList<Object> list = new ArrayList<>();
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("SELECT device_id" +
            "    ,`type` " +
            "    ,`located_cabinet`" +
            "    ,`business_type`" +
            "    ,`ip`" +
            "    ,`room`" +
            "    ,`create_time`" +
            "    ,`device_model`" +
            "    ,`administrator`" +
            "    ,`data_center`" +
            "    ,`purchase_date`" +
            "    ,`rated_power`" +
            "    ,`usage_status`" +
            "    ,`location_id`" +
            "    ,`remarks`" +
            "    ,`status`" +
            "    ,`modified_time`" +
            "    ,`save_function`" +
            "    ,`incompatible_feature`" +
            "    ,`manufacturer`" +
            "    ,`addr`  FROM device_common WHERE status = 1");
        if (condition != null && !"".equals(condition)) {
            condition = condition + "%";
            sqlBuffer.append(" AND (device_id LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("ip LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("administrator LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("manufacturer LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("room LIKE ?) ");
            list.add(condition);
        }
        if (types != null && types.length != 0) {
            int length = types.length;
            sqlBuffer.append(" AND type IN (");
            for (int i = 0; i < length; i++) {
                if (i != length - 1) {
                    sqlBuffer.append("?,");
                } else {
                    sqlBuffer.append("?)");
                }
                list.add(types[i].toString());
            }
        }
        if (cabinetSn != null && !"".equals(cabinetSn)) {
            sqlBuffer.append(" AND located_cabinet = ?");
            list.add(cabinetSn);
        }
        if (status != null && !"".equals(status)) {
            sqlBuffer.append(" AND usage_status = ?");
            list.add(status);
        }
        if (business != null && !"".equals(business)) {
            sqlBuffer.append(" AND business_type = ?");
            list.add(business);
        }
        if (locationId != null && !"".equals(locationId)) {
            sqlBuffer.append(" AND location_id = ?");
            list.add(locationId);
        }
        sqlBuffer.append(" ORDER BY create_time DESC LIMIT ?,?");
        list.add(start);
        list.add(size);
        String sql = sqlBuffer.toString();
        Object[] params = list.toArray();
        List<DeviceCommonEntity> deviceCommonEntities = JdbcUtil.queryForList(sql, params, DeviceCommonEntity.class);
        return deviceCommonEntities;
    }

    @Override
    public List<DeviceCommonEntity> findByCondition(String cabinetSn, String condition, Object[] types, String business, String status, String locationId) {
        ArrayList<Object> list = new ArrayList<>();
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("SELECT `device_id`" +
            "    ,`type`" +
            "    ,`located_cabinet`" +
            "    ,`business_type`" +
            "    ,`ip`" +
            "    ,`room`" +
            "    ,`create_time`" +
            "    ,`device_model`" +
            "    ,`administrator`" +
            "    ,`data_center`" +
            "    ,`purchase_date`" +
            "    ,   `rated_power`" +
            "    ,`usage_status`" +
            "    ,`location_id`" +
            "    ,`remarks`" +
            "    ,`status`" +
            "    ,`modified_time`" +
            "    ,`save_function`" +
            "    ,`incompatible_feature`" +
            "    ,`manufacturer`" +
            "    ,`addr`  FROM device_common WHERE `status` = 1");
        if (condition != null && !"".equals(condition)) {
            condition = condition + "%";
            sqlBuffer.append(" AND (device_id LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("ip LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("administrator LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("manufacturer LIKE ? OR ");
            list.add(condition);
            sqlBuffer.append("room LIKE ?) ");
            list.add(condition);
        }
        if (types != null && types.length != 0) {
            int length = types.length;
            sqlBuffer.append(" AND type IN (");
            for (int i = 0; i < length; i++) {
                if (i != length - 1) {
                    sqlBuffer.append("?,");
                } else {
                    sqlBuffer.append("?)");
                }
                list.add(types[i].toString());
            }
        }
        if (cabinetSn != null && !"".equals(cabinetSn)) {
            sqlBuffer.append(" AND located_cabinet = ?");
            list.add(cabinetSn);
        }
        if (status != null && !"".equals(status)) {
            sqlBuffer.append(" AND usage_status = ?");
            list.add(status);
        }
        if (business != null && !"".equals(business)) {
            sqlBuffer.append(" AND business_type = ?");
            list.add(business);
        }
        if (locationId != null && !"".equals(locationId)) {
            sqlBuffer.append(" AND location_id = ?");
            list.add(locationId);
        }
        sqlBuffer.append(" ORDER BY create_time DESC");
        String sql = sqlBuffer.toString();
        Object[] params = list.toArray();
        List<DeviceCommonEntity> deviceCommonEntities = JdbcUtil.queryForList(sql, params, DeviceCommonEntity.class);
        return deviceCommonEntities;
    }

    @Override
    public List<DeviceCommonEntity> findByCabinet(String cabinetSn) {
        //sn可以为空
        //返回值可以能为null
        String sql = "SELECT * FROM device_common WHERE status = ? AND  located_cabinet= ? ";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = cabinetSn;
        List<DeviceCommonEntity> list = JdbcUtil.queryForList(sql, params, DeviceCommonEntity.class);
        return list;
    }

    @Override
    public List<Map<String, String>> findAll() {
        return null;
    }

    @Override
    public Map<String, String> findOne(String deviceId) {
        String sql = "SELECT * FROM device_common WHERE status = ? AND device_id = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = deviceId;
        Map<String, String> map = JdbcUtil.queryForMap(sql, params);
        return map;
    }

    @Override
    public boolean insert(Map<String, String> map) throws SQLException {
        //map不能为null
        String sql = "REPLACE INTO device_common(device_id,type,located_cabinet,business_type,ip," +
            "room,create_time,device_model,administrator,data_center,purchase_date,rated_power," +
            "location_id,remarks,status,modified_time,usage_status,save_function,incompatible_feature,manufacturer,addr) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,?,?,?,?,?,?)";
        Object[] params = new Object[20];
        params[0] = map.get("device_id");
        params[1] = map.get("type");
        params[2] = map.get("located_cabinet");
        params[3] = map.get("business_type");
        params[4] = map.get("ip");
        params[5] = map.get("room");
        params[6] = map.get("create_time");
        params[7] = map.get("device_model");
        params[8] = map.get("administrator");
        params[9] = map.get("data_center");
        params[10] = map.get("purchase_date");
        //rated_power 必须为数字类型
        params[11] = map.get("rated_power");
        params[12] = map.get("location_id");
        params[13] = map.get("remarks");
        params[14] = map.get("modified_time");
        params[15] = map.get("usage_status");
        params[16] = map.get("save_function");
        params[17] = map.get("incompatible_feature");
        params[18] = map.get("manufacturer");
        params[19] = map.get("addr");
        boolean result = JdbcUtil.update1(sql, params);
        return result;
    }

    @Override
    public boolean update(Map<String, String> map) throws SQLException {
        //map不为空
        StringBuffer sqlBuffer = new StringBuffer();
        sqlBuffer.append("UPDATE device_common SET ");
        ArrayList<String> paramList = new ArrayList<>();
        Iterator<Map.Entry<String, String>> iterator = map.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> entry = (Map.Entry) iterator.next();
            String key = entry.getKey();
            Field field = null;
            try {
                field = DeviceCommonEntity.class.getDeclaredField(key);
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
            if (key != null && !"device_id".equals(key) && field != null) {
                sqlBuffer.append(key);
                sqlBuffer.append("=?,");
                paramList.add(entry.getValue());
            }
        }
        sqlBuffer.append("status=1");
        sqlBuffer.append(" WHERE device_id=?");
        paramList.add(map.get("device_id"));
        String sql = sqlBuffer.toString();
        Object[] params = paramList.toArray();
        boolean result = JdbcUtil.update1(sql, params);
        return result;
    }

    @Override
    public boolean delete(String deviceId) throws SQLException {
        //假删除
        String sql = "UPDATE device_common SET status = 0 WHERE 1= ? and device_id = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = deviceId;
        boolean result = JdbcUtil.update1(sql, params);
        return result;
    }

    @Override
    public boolean modifyUsageStatus(String deviceId, String usageStatus) throws SQLException {
        String sql = "UPDATE device_common SET usage_status = ? WHERE 1= ? and device_id = ?";
        Object[] params = new Object[3];
        params[0] = usageStatus;
        params[1] = 1;
        params[2] = deviceId;
        boolean result = JdbcUtil.update1(sql, params);
        return result;
    }

    @Override
    public boolean delete(String[] deviceIds) throws SQLException {
        //sns可以为空
        StringBuffer sqlBuffer = new StringBuffer();
        int length = deviceIds.length;
        sqlBuffer.append("UPDATE device_common SET status = 0 WHERE device_id IN ");
        sqlBuffer.append("(");
        for (int i = 0; i < length; i++) {
            if (i == length - 1) {
                sqlBuffer.append("?)");
            } else {
                sqlBuffer.append("?,");
            }
        }
        String sql = sqlBuffer.toString();
        boolean result = JdbcUtil.update1(sql, deviceIds);
        return result;
    }

    @Override
    public List<DeviceCommonEntity> findByType(String type) {
        //type 为非空
        //返回值可能为null
        String sql = "SELECT device_id,type,ip,room,administrator,create_time,modified_time,rated_power,usage_status,business_type FROM device_common WHERE status = ? AND type = ?";
        Object[] params = new Object[2];
        params[0] = 1;
        params[1] = type;
        List<DeviceCommonEntity> list = JdbcUtil.queryForList(sql, params, DeviceCommonEntity.class);
        return list;
    }

    @Override
    public List<Map<String, String>> findByTypes(String types) {
        StringBuffer sqlBuffer = new StringBuffer();

        sqlBuffer.append("SELECT device_id,type,ip from device_common WHERE status=1");
        if(types == null || "".equals(types)){
            String sql = sqlBuffer.toString();
            return JdbcUtil.queryForList_Map(sql, null);
        }
        String[] typeArray = types.split(",");
        int length = typeArray.length;
        sqlBuffer.append(" AND type IN (");
        for (int i = 0; i < length; i++) {
            if (i == length - 1) {
                sqlBuffer.append("?)");
            } else {
                sqlBuffer.append("?,");
            }
        }
        String sql = sqlBuffer.toString();
        return JdbcUtil.queryForList_Map(sql, typeArray);
    }

    @Override
    public Map<String, String>  usedServerAmount() {
        String sql="SELECT COUNT(1) as count from device_common WHERE usage_status in(?) and status=1 and type= ?";
        Object[] kk = new Object[2];
        kk[0] ="1";
        //kk[1] ="capping";
        //kk[2] ="free";
        kk[1] ="server";
        Map<String, String> map = JdbcUtil.queryForMap(sql, kk);
        return map;
    }

    @Override
    public Map<String, String> totalServerAmount() {
        String sql="SELECT COUNT(1) as count from device_common where status=1 and type= ?";
        Object[] kk = new Object[1];
        kk[0] ="server";
        Map<String, String> map = JdbcUtil.queryForMap(sql,kk);
        return map;
    }
    /**
     * 查询出arm 数据库
     * 2021/03/30 修改  需求是arm 和x86 都需要走董雪的agent
     * @return
     */
    @Override
    public List<DeviceIPDeviceIdDTO> selectServerIp() {
        String sql = "select dc.ip,ds.device_id ,server_type from  device_server ds " +
            " inner join device_common dc on  ds.device_id =dc.device_id " +
            " where ds.status=?  and dc.status=1  ";
        Object[] params = new Object[1];
        params[0] = "1";
        return JdbcUtil.queryForList(sql, params, DeviceIPDeviceIdDTO.class);
    }
}
